﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Vowei.Core.Models;
using Vowei.Data.Helpers;
using Vowei.Data.Models;
using Vowei.Converters;
using Vowei.Converters.Helpers;
using System.Diagnostics;
using Vowei.Core;

namespace Vowei.Data.Converters.Excel
{
    public class UserExcelConverter : ExcelConverter<IEnumerable<IUser>>
    {
        private Employee _currentUser;

        public UserExcelConverter()
            : this("Member History", null, DefaultTranslator)
        { 
        }

        public UserExcelConverter(string sheetName)
            : this(sheetName, null, DefaultTranslator)
        {
        }

        public UserExcelConverter(Employee currentUser)
            : this("Member History", currentUser, DefaultTranslator)
        {
        }

        public UserExcelConverter(string sheetName, Employee currentUser, ITranslator translator)
            : base(translator)
        {
            WorkSheetName = sheetName;
            _currentUser = currentUser;
        }

        public override bool CanConvert(Stream stream)
        {
            if (stream == null)
                throw new ArgumentNullException("stream");
            if (Translator == null)
                throw new InvalidOperationException(DefaultTranslator.T("在从Excel导入数据之前，请指定用户转换日期、数字、货币等格式的区域设置！"));

            var memoryStream = new MemoryStream((int)stream.Length);
            stream.CopyTo(memoryStream);
            var workbook = new HSSFWorkbook(memoryStream);
            var sheet = GetWorkItemsSheet(workbook);
            var firstRow = GuessFirstRow(sheet, Translator.T<Employee, string>(m => m.Email), false);
            return firstRow >= 0;
        }

        public override IEnumerable<IUser> ConvertFrom(Stream stream)
        {
            if (stream == null)
                throw new ArgumentNullException("stream");
            if (Translator == null)
                throw new InvalidOperationException(DefaultTranslator.T("在从Excel导入数据之前，请指定用户转换日期、数字、货币等格式的区域设置！"));

            var memoryStream = new MemoryStream((int)stream.Length);
            stream.CopyTo(memoryStream);
            var workbook = new HSSFWorkbook(memoryStream);
            var sheet = workbook.GetSheet(WorkSheetName);
            if (sheet == null)
                sheet = workbook.GetSheetAt(0);
            var map = ReadColumnNames(sheet.GetRow(sheet.FirstRowNum), new string[] {
                Translator.T<Employee, string>(m => m.DisplayName), // "Name",
                Translator.T<Employee, string>(m => m.UserName), // "User Account",
                Translator.T<Employee, string>(m => m.Email), // "Email",
                Translator.T<Employee, string>(m => m.Department), // "Department",
                Translator.T<Employee, string>(m => m.Telephone), // "Telephone",
                Translator.T<Employee, string>(m => m.Level), // "Level",
                Translator.T<Employee, string>(m => m.Title), // "Title",
                Translator.T<Employee, DateTime>(m => m.OnboardDate), // "Onboard Date",
                Translator.T<Employee, DateTime?>(m => m.PotentialQuitDate), // "Potential Quit Date",
                Translator.T<Employee, bool>(m => m.IsConfirmed), // "Confirmed",
                Translator.T<Employee, Employee>(m => m.ReportTo), // Translator.T<Employee, Employee>(m => m.ReportTo),
                Translator.T<Employee, Employee>(m => m.Replacement), // Translator.T<Employee, Employee>(m => m.Replacement)
            });
            var employees = new List<Employee>();

            for (int i = sheet.FirstRowNum + 1; i < sheet.LastRowNum + 1; ++i)
            {
                var row = sheet.GetRow(i);

                try
                {
                    var employee = new Employee()
                    {
                        DisplayName = ReadCell<string>(row, map[Translator.T<Employee, string>(m => m.DisplayName)], false),
                        UserName = ReadCell<string>(row, map[Translator.T<Employee, string>(m => m.UserName)], false),
                        Email = ReadCell<string>(row, map[Translator.T<Employee, string>(m => m.Email)], false),
                        Department = ReadCell<string>(row, map[Translator.T<Employee, string>(m => m.Department)], false),
                        Telephone = ReadCell<string>(row, map[Translator.T<Employee, string>(m => m.Telephone)], false),
                        Level = ReadCell<string>(row, map[Translator.T<Employee, string>(m => m.Level)], false),
                        Title = ReadCell<string>(row, map[Translator.T<Employee, string>(m => m.Title)], false),
                        PotentialQuitDate = ReadDateCell(row, map[Translator.T<Employee, DateTime?>(m => m.PotentialQuitDate)]),
                        IsConfirmed = TryConvertBoolean(ReadCell<string>(row, map[Translator.T<Employee, bool>(m => m.IsConfirmed)], false))
                    };

                    var date = ReadDateCell(row, map[Translator.T<Employee, DateTime>(m => m.OnboardDate)]);
                    if (date == null)
                        Trace.TraceError(string.Format(Translator.T("在Excel文件的“{0}”工作表里，第{1}行的“Onboard Date”列有误，该列不能为空！"), sheet.SheetName, i + 1));
                    else
                        employee.OnboardDate = date.Value;

                    if (!string.IsNullOrEmpty(employee.Email) && !EntityHelpers.MatchUserEmail(employee.Email))
                        throw new InvalidDataException(string.Format(Translator.T("在Excel文件的“{0}”工作表里，第{1}行的“Email”列有误，电子邮件格式不符合要求，合法的电子邮件在@字符前可以包括字母数字以及“._%+-”等字符，在@字符后，只能包含合法的域名！"), sheet.SheetName, i + 1));

                    if (string.IsNullOrEmpty(employee.Email) &&
                         string.IsNullOrEmpty(employee.UserName))
                        throw new InvalidDataException(string.Format(Translator.T("在Excel文件的“{0}”工作表里，第{1}行的\"Email\"或者\"User Account\"列至少有一列不为空！"), sheet.SheetName, i + 1));

                    employees.Add(employee);
                }
                catch (Exception e)
                {
                    Trace.TraceError(string.Format(Translator.T("在试图导入Excel文件的“{0}”工作表失败，第{1}行有误, 具体错误信息是：{2}"), sheet.SheetName, i + 1, e.Message), e);
                }
            }

            var sortedEmailEmpls = employees.ToArray();
            var sortedNameEmpls = employees.ToArray();
            var sortedUserNameEmpls = employees.ToArray();
            Array.Sort(sortedEmailEmpls, (left, right) => string.CompareOrdinal(left.Email, right.Email));
            Array.Sort(sortedNameEmpls, (left, right) => string.CompareOrdinal(left.DisplayName, right.DisplayName));
            Array.Sort(sortedUserNameEmpls, (left, right) => string.CompareOrdinal(left.UserName, right.UserName));

            for (int i = sheet.FirstRowNum + 1, j = 0; i < sheet.LastRowNum + 1 && j < employees.Count; ++i, ++j)
            {
                var row = sheet.GetRow(i);

                try
                {
                    if (EntityHelpers.MatchUserEmail(ReadCell<string>(row, map[Translator.T<Employee, Employee>(m => m.ReportTo)], false)))
                        employees[j].ReportTo = FixAssociationByEmployeeKey(
                            row, i, map[Translator.T<Employee, Employee>(m => m.ReportTo)], employees, sortedEmailEmpls, e => e.Email);
                    
                    if ( employees[j].ReportTo == null )
                        employees[j].ReportTo = FixAssociationByEmployeeKey(
                            row, i, map[Translator.T<Employee, Employee>(m => m.ReportTo)], employees, sortedUserNameEmpls, e => e.UserName);

                    if (employees[j].ReportTo == null)
                        employees[j].ReportTo = FixAssociationByEmployeeKey(
                            row, i, map[Translator.T<Employee, Employee>(m => m.ReportTo)], employees, sortedNameEmpls, e => e.DisplayName);

                    if (EntityHelpers.MatchUserEmail(ReadCell<string>(row, map[Translator.T<Employee, Employee>(m => m.Replacement)], false)))
                        employees[j].Replacement = FixAssociationByEmployeeKey(
                            row, i, map[Translator.T<Employee, Employee>(m => m.Replacement)], employees, sortedEmailEmpls, e => e.Email);

                    if (employees[j].Replacement == null)
                        employees[j].Replacement = FixAssociationByEmployeeKey(
                            row, i, map[Translator.T<Employee, Employee>(m => m.Replacement)], employees, sortedUserNameEmpls, e => e.UserName);

                    if (employees[j].Replacement == null)
                        employees[j].Replacement = FixAssociationByEmployeeKey(
                            row, i, map[Translator.T<Employee, Employee>(m => m.Replacement)], employees, sortedNameEmpls, e => e.DisplayName);

                }
                catch (Exception e)
                {
                    throw new InvalidDataException(string.Format(Translator.T("在试图导入Excel文件的“{0}”工作表失败，第{1}行有误, 具体错误信息是：{2}"), WorkSheetName, i + 1, e.Message), e);
                }
            }

            return employees;
        }

        public override void ConvertTo(IEnumerable<IUser> users, Stream stream)
        {
            if (string.IsNullOrEmpty(TemplateExcel))
                throw new InvalidOperationException("在生成Excel文件之前,请通过设置TemplateExcel属性指定Excel文件的模板！");

            using (var template = new FileStream(TemplateExcel, FileMode.Open, FileAccess.Read))
            {
                using (var workbook = new HSSFWorkbook(template))
                {
                    var sheet = workbook.GetSheet(WorkSheetName);
                    workbook.DocumentSummaryInformation = GetDocumentSummaryInformation(_currentUser);
                    workbook.SummaryInformation = GetSummaryInformation();
                    var patr = sheet.CreateDrawingPatriarch();
                    Cell cell = null;

                    for (int i = 0; i < users.Count(); ++i)
                    {
                        // 跳过列头
                        var row = sheet.CreateRow(i + 1);
                        var user = users.ElementAt(i) as Employee;
                        // Name
                        row.CreateCell(0).SetCellValue(user.DisplayName);

                        // Reports_to
                        if (user.ReportTo != null)
                        {
                            cell = row.CreateCell(1);
                            cell.SetCellValue(user.ReportTo.DisplayName);
                            AddCellComment(patr, cell, user.ReportTo.Email);
                        }

                        // Title
                        row.CreateCell(2).SetCellValue(user.Title);

                        // Department
                        row.CreateCell(3).SetCellValue(user.Department);
                        row.CreateCell(4).SetCellValue(user.Telephone);
                        row.CreateCell(5).SetCellValue(user.Level);
                        row.CreateCell(6).SetCellValue(user.Email);
                        row.CreateDateCell(7, user.OnboardDate);
                        row.CreateDateCell(8, user.PotentialQuitDate);
                        row.CreateCell(9).SetCellValue(user.IsConfirmed ? Translator.T("Y") : Translator.T("N"));

                        // Replacement
                        if (user.Replacement != null)
                        {
                            cell = row.CreateCell(10);
                            cell.SetCellValue(user.Replacement.DisplayName);
                            AddCellComment(patr, cell, user.Replacement.Email);
                        }
                    }

                    workbook.Write(stream);
                }
            }
        }

        private void AddCellComment(Drawing patr, Cell cell, string text)
        {
            var comment = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex + 1, cell.RowIndex, cell.ColumnIndex + 3, cell.RowIndex + 3));
            comment.Visible = false;
            comment.String = new HSSFRichTextString(text);

            if ( _currentUser != null )
                comment.Author = _currentUser.DisplayName;

            cell.CellComment = comment;
        }

        protected SummaryInformation GetSummaryInformation()
        {
            var si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "人员";
            si.Author = _currentUser.DisplayName;
            si.CreateDateTime = DateTime.Now;
            si.LastAuthor = _currentUser.DisplayName;
            si.Keywords = "人员管理";

            return si;
        }

        protected DocumentSummaryInformation GetDocumentSummaryInformation(Employee user)
        {
            var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = user.Company;
            dsi.Category = "人员管理";

            return dsi;
        }

        private Employee FixAssociationByEmployeeKey(
            Row row, int i, int columnNum, 
            List<Employee> employees, Employee[] sortedEmpls, Func<Employee, string> employeeKey)
        {
            var comparer = new EmployeeComparer(employeeKey);
            var empSearch = ReadCell<string>(row, columnNum, false);

            if (!string.IsNullOrEmpty(empSearch))
                empSearch = empSearch.Trim();

            // 有直接经理
            if (!string.IsNullOrEmpty(empSearch))
            {
                var idx = Array.BinarySearch(sortedEmpls, empSearch, comparer);

                if (idx >= 0)
                    return sortedEmpls[idx];
            }
                
            return null;
        }
    }
}
